package dao;

import pojo.Book;
import utils.Page;
import vo.BookVO;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl<T> extends BaseDao implements BookDao{
    @Override
    public void getBooList(BookVO book, Page page) throws SQLException {

        String sql =" SELECT `book_id`, `book_ISBN`, `book_name`, `book_author`, `book_pub`" +
                ", `book_sort` , `book_abstract`, `book_total`, `book_photo`, `book_pub_location`" +
                ", `book_pub_year`,bs.sort_id ,bs.`sort_name`" +
                " from book b,book_sort  bs" +
                " where b.book_sort = bs.sort_id ";//sql   //阿里规范
        StringBuilder builder =new StringBuilder(sql);
        List<Object> params = new ArrayList<>();

        //将查询字段公用
        getQueryCondition(builder,book,params);

        builder.append(" order by book_pub_year desc");
        builder.append(" limit ? ,?");//10

        params.add((page.getCurrPageNo()-1)*page.getPageSize()); // ? service
        params.add(page.getPageSize());

        List<BookVO> bookVOList= super.executeQuery(builder.toString(),new BookVO(),params.toArray());

        //设置总笔数(共?笔数据)
        int count =getBooksCount(book);
        page.setTotalCount(count);
        page.setList(bookVOList);
    }

    @Override
    public Integer getBooksCount(BookVO book) throws SQLException {

        StringBuilder builder =new StringBuilder("select count(b.book_id) from book b,book_sort bs" +
                " where 1=1" +
                " and bs.sort_id = b.book_sort");

        List<Object> params = new ArrayList<>();

        getQueryCondition(builder,book,params);

        Long count = (Long) executeScalar(builder.toString(),params.toArray());
        //执行查询
        return count.intValue() ;
    }

    @Override
    public Integer modifyBook(Book book, String action) throws SQLException {
        String sql="";
        List<Object> paramsList= new ArrayList<>();
        switch (action){
            case "insert":
                sql ="INSERT INTO `book`(`book_ISBN`, `book_name`, `book_author`, `book_pub`, `book_sort`" +
                        ", `book_abstract`, `book_total`, `book_photo`, `book_pub_location`, `book_pub_year`" +
                        ") VALUES ( ?,?,?,?,?,?,?,?,?,? )";
                paramsList.add(book.getBookIsbn());
                paramsList.add(book.getBookName());
                paramsList.add(book.getBookAuthor());
                paramsList.add(book.getBookPub());
                paramsList.add(book.getBookSort());
                paramsList.add(book.getBookAbstract());
                paramsList.add(book.getBookTotal());
                paramsList.add(book.getBookPhoto());
                paramsList.add(book.getBookPubLocation());
                paramsList.add(book.getBookPubYear());
                return executeUpdate(sql,paramsList.toArray());
            case "update":
                // photo
                sql =" UPDATE `book` SET `book_ISBN` = ?, `book_name` =?, `book_author` = ?, `book_pub` = ?, `book_sort` = ?, `book_abstract` = ?, `book_total` = ?, \n" +
                        "`book_photo` = ?, " +
                        "`book_pub_location` = ?, `book_pub_year` = ?, `sort_name` = ? " +
                        "WHERE `book_id` = ?  ";
                paramsList.add(book.getBookIsbn());
                paramsList.add(book.getBookName());
                paramsList.add(book.getBookAuthor());
                paramsList.add(book.getBookPub());
                paramsList.add(book.getBookSort());
                paramsList.add(book.getBookTotal());
                paramsList.add(book.getBookPubLocation());
                paramsList.add(book.getBookPubYear());
                paramsList.add(book.getBookId());
                return executeUpdate(sql,paramsList.toArray());
            case "delete":
                break;
        }
        return null;
    }

    private void getQueryCondition(StringBuilder builder ,BookVO book,List<Object> params){
        //StringBuilder builder =new StringBuilder(sql);
        //List<Object> params = new ArrayList<>();
        if (null != book){
            if (null != book.getBookId() && book.getBookId()>0){
                builder.append(" and book_id = ? ");//sql +=" and book_id = ? ";
                params.add(book.getBookId());
            }

            if (null != book.getBookName() && !"".equals(book.getBookName())){
                String bookName ="%".concat(book.getBookName()).concat("%"); // %人%
                builder.append(" and book_name like ? ");
                params.add(bookName);
            }
            if (null !=book.getSort_id() && book.getSort_id()>0){
                builder.append(" and bs.sort_id=?");
                params.add(book.getSort_id());
            }

            if (null != book.getBookPub() && !"".equals(book.getBookPub())){
                String book_pub ="%".concat(book.getBookPub()).concat("%"); // %人%
                builder.append(" and book_pub like ? ");
                params.add(book_pub);
            }
        }
    }


}
